【MySQL】调用储存过程显示 ERROR 1305 (42000): PROCEDURE test.sp1 does not exist |
您所在的位置：网站首页 › no rows affected翻译 › 【MySQL】调用储存过程显示 ERROR 1305 (42000): PROCEDURE test.sp1 does not exist |
|
问题描述：
1.在MySQL中创建了一个简单的查询存储过程： mysql> CREATE PROCEDURE spl() SELECT VERSION(); Query OK, 0 rows affected (0.01 sec)2.然后用CALL调用此存储过程，报错 ERROR 1305 (42000): PROCEDURE test.sp1 does not exist： mysql> CALL sp1; ERROR 1305 (42000): PROCEDURE test.sp1 does not exist 问题排查：1.首先考虑是否是此储存过程当真不在，查看当前存储过程，发现存储过程存在： mysql> SHOW PROCEDURE STATUS; +------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | test | spl | PROCEDURE | root@localhost | 2019-02-28 13:00:02 | 2019-02-28 13:00:02 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci | +------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 row in set (0.01 sec)2.此时想到是用户没有当前存储过程的调用权限，赋予存储过程权限给当前用户，此时又出现了一个错误： mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; ERROR 1133 (42000): Can't find any matching row in the user table通过查询资料发现，需要先将当前的存储过程刷新，再执行授权语句： mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@localhost; Query OK, 0 rows affected (0.00 sec)3.再执行调用此存储过程，显示成功调用： mysql> SHOW PROCEDURE STATUS; +------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | test | spl | PROCEDURE | root@localhost | 2019-02-28 13:11:23 | 2019-02-28 13:11:23 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci | +------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 row in set (0.01 sec) mysql> CALL spl; +-----------+ | VERSION() | +-----------+ | 5.5.40 | +-----------+ 1 row in set (0.00 sec)
|
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |